﻿-- Function: pl_function.copiar_declaracion(character, character, character, integer, integer, integer, character varying, character varying)

DROP FUNCTION IF EXISTS pl_function.copiar_declaracion(character, character, character, integer, integer, integer, character varying, character varying);

CREATE OR REPLACE FUNCTION pl_function.copiar_declaracion(p_mhresum character, p_mhresum_old character, p_dpredio character, p_tipoacc integer, p_cperini integer, p_cperfin integer, p_vhostnm character varying, p_vusernm character varying, p_mpropie character varying)
  RETURNS void AS
$BODY$
declare
	ids_mpropie numeric(18, 0) := 0;
	ids_dpredio numeric(18, 0) := 0;
	ids_mconstr numeric(18, 0) := 0;
	ids_minstal numeric(18, 0) := 0;
	ids_mrustic numeric(18, 0) := 0;
	ids_vpredio numeric(18, 0) := 0;
	ids_dconstr numeric(18, 0) := 0;
	ids_dinstal numeric(18, 0) := 0;
	ids_drustic numeric(18, 0) := 0;
begin
	
	create temporary table dpredio_temp(
		  mpredio character(10)
		, ctippre character(10)
		, cclasif character(10)
		, ccondic character(10)
		, cestado character(10)
		, cusogen character(10)
		, cusoesp character(10)
		, nporcen numeric(10,2)
		, ntertot numeric(18,5)
		, nporter numeric(10,2)
		, nterren numeric(18,5)
		, ncomtot numeric(18,5)
		, nporcom numeric(10,2)
		, narecom numeric(18,5)
		, nporafe numeric(10,2)
		, dfecadq timestamp without time zone
		, dfecdes timestamp without time zone
		, dafecta timestamp without time zone
		, nfrente numeric(18,5)
		, ncanper integer
		, ctippar character(10)
		, vobserv character varying(500)
		, nestado integer
		, vhostnm character varying(25)
		, vusernm character varying(25)
		, ddatetm timestamp without time zone
		, dfectra timestamp with time zone
		, cnotari character varying(100)
		, ctiptra character varying(500)
		, cpartid character varying(50)
		, csubtip character varying(10)
		, cmotadq character varying(10)
		, mhresum character(10)
		, dpredio character(10)
		, idsigma integer
	) on commit drop;
	
	create temporary table mconstr_temp(
		  dpredio character(10)
		, cnitems integer
		, cnumpis character varying(5)
		, cmescon character(2)
		, canocon character(4)
		, cmateri character varying(10)
		, cconser character varying(10)
		, cmurcol character varying(10)
		, ctechos character varying(10)
		, cmpisos character varying(10)
		, cpueven character varying(10)
		, crevest character varying(10)
		, cbanios character varying(10)
		, celectr character varying(10)
		, narecom numeric(18,5)
		, nporcom numeric(18,5)
		, nestado integer
		, vhostnm character varying(25)
		, vusernm character varying(25)
		, ddatetm timestamp without time zone
		, narecon numeric(10,5)
		, mconstr character(10)
		, idsigma integer
	) on commit drop;
	
	create temporary table minstal_temp(
		  dpredio character(10)
		, cnumpis character(5)
		, canocon character(4)
		, ctipins character(10)
		, nvalanc numeric(10,5)
		, nvalalt numeric(10,5)
		, nvallar numeric(10,5)
		, ctipint character(10)
		, vdesint character varying(300)
		, cmateri character varying(10)
		, cconser character varying(10)
		, cmurcol character varying(10)
		, ctechos character varying(10)
		, cmpisos character varying(10)
		, cpueven character varying(10)
		, crevest character varying(10)
		, cbanios character varying(10)
		, celectr character varying(10)
		, narecom numeric(10,5)
		, nporcom numeric(10,5)
		, nestado integer
		, vhostnm character varying(25)
		, vusernm character varying(25)
		, ddatetm timestamp without time zone
		, minstal character(10)
		, idsigma integer
	) on commit drop;
	
	create temporary table mrustic_temp(
		  dpredio character(10)
		, cubinor character varying(50)
		, cubisur character varying(50)
		, cubiest character varying(50)
		, cubioes character varying(50)
		, cpronor character varying(50)
		, cprosur character varying(50)
		, cproest character varying(50)
		, cprooes character varying(50)
		, cpculti character varying(25)
		, canagua character varying(25)
		, nestado integer
		, vhostnm character varying(25)
		, vusernm character varying(25)
		, ddatetm timestamp without time zone
		, idsigma integer
	) on commit drop;

	insert into dpredio_temp
	select
		  mpredio, ctippre, cclasif, ccondic
		, cestado, cusogen, cusoesp, nporcen
		, ntertot, nporter, nterren, ncomtot
		, nporcom, narecom, nporafe, dfecadq
		, dfecdes, dafecta, nfrente, ncanper
		, ctippar, vobserv, nestado, vhostnm
		, vusernm, ddatetm, dfectra, cnotari
		, ctiptra, cpartid, csubtip, cmotadq
		, mhresum, idsigma, row_number() over(order by idsigma)
	from (
		select
			  mpredio, ctippre, cclasif, ccondic
			, cestado, cusogen, cusoesp, nporcen
			, ntertot, nporter, nterren, ncomtot
			, nporcom, narecom, nporafe, dfecadq
			, dfecdes, dafecta, nfrente, ncanper
			, ctippar, vobserv, nestado, p_vhostnm vhostnm
			, p_vusernm vusernm, now() ddatetm, dfectra, cnotari
			, ctiptra, cpartid, csubtip, cmotadq
			, p_mhresum mhresum, idsigma
		from registro.dpredio where mhresum=p_mhresum_old
		union all
		select
			  mpredio, ctippre, cclasif, ccondic
			, cestado, cusogen, cusoesp, nporcen
			, ntertot, nporter, nterren, ncomtot
			, nporcom, narecom, nporafe, dfecadq
			, dfecdes, dafecta, nfrente, ncanper
			, ctippar, vobserv, nestado, p_vhostnm vhostnm
			, p_vusernm vusernm, now() ddatetm, dfectra, cnotari
			, ctiptra, cpartid, csubtip, cmotadq
			, p_mhresum mhresum, idsigma
		from registro.dpredio where idsigma=p_dpredio
	) a;
	
	insert into mconstr_temp(idsigma
		, dpredio, cnitems, cnumpis, cmescon
		, canocon, cmateri, cconser, cmurcol
		, ctechos, cmpisos, cpueven, crevest
		, cbanios, celectr, narecom, nporcom
		, nestado, vhostnm, vusernm, ddatetm
		, narecon, mconstr
	)
	select
		  row_number() over(order by a.idsigma) idsigma
		, a.dpredio, a.cnitems, a.cnumpis, a.cmescon
		, a.canocon, a.cmateri, a.cconser, a.cmurcol
		, a.ctechos, a.cmpisos, a.cpueven, a.crevest
		, a.cbanios, a.celectr, a.narecom, a.nporcom
		, a.nestado, p_vhostnm vhostnm, p_vusernm vusernm, now() ddatetm
		, a.narecon, a.idsigma
	from dpredio_temp b inner join registro.mconstr a on b.dpredio=a.dpredio;

	insert into minstal_temp(
		  dpredio, cnumpis, canocon, ctipins
		, nvalanc, nvalalt, nvallar, ctipint
		, vdesint, cmateri, cconser, cmurcol
		, ctechos, cmpisos, cpueven, crevest
		, cbanios, celectr, narecom, nporcom
		, nestado, vhostnm, vusernm, ddatetm
		, minstal, idsigma)
	select
		  a.dpredio, a.cnumpis, a.canocon, a.ctipins
		, a.nvalanc, a.nvalalt, a.nvallar, a.ctipint
		, a.vdesint, a.cmateri, a.cconser, a.cmurcol
		, a.ctechos, a.cmpisos, a.cpueven, a.crevest
		, a.cbanios, a.celectr, a.narecom, a.nporcom
		, a.nestado, p_vhostnm vhostnm, p_vusernm vusernm, now() ddatetm
		, a.idsigma minstal
		, row_number() over(order by a.idsigma) idsigma
	from dpredio_temp b inner join registro.minstal a on b.dpredio=a.dpredio;
	
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids_dpredio from registro.dpredio;
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids_vpredio from registro.vpredio;
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids_mconstr from registro.mconstr;
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids_dconstr from registro.dconstr;
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids_minstal from registro.minstal;
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids_dinstal from registro.dinstal;
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids_mrustic from registro.mrustic;
	select coalesce(max(idsigma)::numeric(18, 0), 0) into ids_drustic from registro.drustic;

	if p_tipoacc = 1 then
		perform pl_function.guardar_mpropie(p_mpropie, p_mhresum, p_vusernm, p_vhostnm);
	end if;
	
	insert into registro.dpredio(idsigma
		, mpredio, ctippre, cclasif, ccondic
		, cestado, cusogen, cusoesp, nporcen
		, ntertot, nporter, nterren, ncomtot
		, nporcom, narecom, nporafe, dfecadq
		, dfecdes, dafecta, nfrente, ncanper
		, ctippar, vobserv, nestado, vhostnm
		, vusernm, ddatetm, dfectra, cnotari
		, ctiptra, cpartid, csubtip, cmotadq
		, mhresum
	)
	select
		  lpad((idsigma + ids_dpredio)::varchar, 10, '0') idsigma
		, mpredio, ctippre, cclasif, ccondic
		, cestado, cusogen, cusoesp, nporcen
		, ntertot, nporter, nterren, ncomtot
		, nporcom, narecom, nporafe, dfecadq
		, dfecdes, dafecta, nfrente, ncanper
		, ctippar, vobserv, nestado, vhostnm
		, vusernm, ddatetm, dfectra, cnotari
		, ctiptra, cpartid, csubtip, cmotadq
		, mhresum
	from dpredio_temp;
	
	insert into registro.vpredio(
		  idsigma, dpredio, cperiod, narance
		, nvalpis, nvalins, nvalter, nvalpre
		, nporafe, nvalafe, nestado, vhostnm
		, vusernm, ddatetm, nbarrid, nparque
		, nrecole, nserena
	)
	select 
		  lpad(((row_number() over(order by a.idsigma)) + ids_vpredio)::varchar, 10, '0') idsigma
		, lpad((b.idsigma + ids_dpredio)::varchar, 10, '0') dpredio
		, a.cperiod, a.narance
		, a.nvalpis, a.nvalins, a.nvalter, a.nvalpre
		, a.nporafe, a.nvalafe, a.nestado, b.vhostnm
		, b.vusernm, b.ddatetm, a.nbarrid, a.nparque
		, a.nrecole, a.nserena
	from dpredio_temp b inner join registro.vpredio a on b.dpredio=a.dpredio and a.cperiod::int>=p_cperini;
	
	insert into registro.mconstr(idsigma
		, dpredio, cnitems, cnumpis, cmescon
		, canocon, cmateri, cconser, cmurcol
		, ctechos, cmpisos, cpueven, crevest
		, cbanios, celectr, narecom, nporcom
		, nestado, vhostnm, vusernm, ddatetm
		, narecon
	)
	select
		  lpad((a.idsigma + ids_mconstr)::varchar, 10, '0') idsigma
		, lpad((b.idsigma + ids_dpredio)::varchar, 10, '0') dpredio
		, a.cnitems, a.cnumpis, a.cmescon
		, a.canocon, a.cmateri, a.cconser, a.cmurcol
		, a.ctechos, a.cmpisos, a.cpueven, a.crevest
		, a.cbanios, a.celectr, a.narecom, a.nporcom
		, a.nestado, a.vhostnm, a.vusernm, a.ddatetm
		, a.narecon
	from dpredio_temp b inner join mconstr_temp a on b.dpredio=a.dpredio;

	insert into registro.dconstr(idsigma
		, mconstr, nantigu, nvaluni, nincrem
		, npordep, ndepred, nvalare, nvalcom
		, nvalpis, nestado, vhostnm, vusernm
		, ddatetm, cperiod)
	select
		  lpad(((row_number() over(order by a.idsigma)) + ids_dconstr)::varchar, 10, '0') idsigma
		, lpad((b.idsigma + ids_mconstr)::varchar, 10, '0') mconstr
		, a.nantigu, a.nvaluni, a.nincrem
		, a.npordep, a.ndepred, a.nvalare, a.nvalcom
		, a.nvalpis, a.nestado, b.vhostnm, b.vusernm
		, b.ddatetm, a.cperiod
	from mconstr_temp b inner join registro.dconstr a on b.mconstr=a.mconstr and a.cperiod::int>=p_cperini;

	insert into registro.minstal(
		  dpredio, cnumpis, canocon, ctipins
		, nvalanc, nvalalt, nvallar, ctipint
		, vdesint, cmateri, cconser, cmurcol
		, ctechos, cmpisos, cpueven, crevest
		, cbanios, celectr, narecom, nporcom
		, nestado, vhostnm, vusernm, ddatetm
		, idsigma)
	select
		  lpad((b.idsigma + ids_dpredio)::varchar, 10, '0') dpredio
		, a.cnumpis, a.canocon, a.ctipins
		, a.nvalanc, a.nvalalt, a.nvallar, a.ctipint
		, a.vdesint, a.cmateri, a.cconser, a.cmurcol
		, a.ctechos, a.cmpisos, a.cpueven, a.crevest
		, a.cbanios, a.celectr, a.narecom, a.nporcom
		, a.nestado, a.vhostnm, a.vusernm, a.ddatetm
		, lpad((a.idsigma + ids_minstal)::varchar, 10, '0') idsigma
	from dpredio_temp b inner join minstal_temp a on b.dpredio=a.dpredio;

	insert into registro.dinstal(
		  idsigma, minstal, nantigu, nvaluni
		, nincrem, npordep, ndepred, nvalare
		, nvalcom, nvalins, nestado, vhostnm
		, vusernm, ddatetm, cperiod)
	 select
		  lpad(((row_number() over(order by a.idsigma)) + ids_dinstal)::varchar, 10, '0') idsigma
		, lpad((b.idsigma + ids_minstal)::varchar, 10, '0') minstal
		, a.nantigu, a.nvaluni, a.nincrem, a.npordep, a.ndepred
		, a.nvalare, a.nvalcom, a.nvalins, a.nestado, b.vhostnm
		, b.vusernm, b.ddatetm, a.cperiod
	from minstal_temp b inner join registro.dinstal a on b.minstal=a.minstal and a.cperiod::int>=p_cperini;

	perform pl_function.guardar_dresumn(p_mhresum, p_vhostnm, p_vusernm);
exception
when sqlstate '57014' then
	raise notice 'Error';
	-- return 0;
end
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pl_function.copiar_declaracion(character, character, character, integer, integer, integer, character varying, character varying, character varying)
  OWNER TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.copiar_declaracion(character, character, character, integer, integer, integer, character varying, character varying, character varying) TO postgres;
GRANT EXECUTE ON FUNCTION pl_function.copiar_declaracion(character, character, character, integer, integer, integer, character varying, character varying, character varying) TO public;
GRANT EXECUTE ON FUNCTION pl_function.copiar_declaracion(character, character, character, integer, integer, integer, character varying, character varying, character varying) TO desarrollo;
